package jnpf.base.service.impl;


import jnpf.base.service.DbLinkService;
import jnpf.base.service.DbSyncService;
import jnpf.base.service.DbTableService;
import jnpf.database.constant.DbFieldConst;
import jnpf.database.datatype.db.DtMySQLEnum;
import jnpf.database.datatype.db.DtOracleEnum;
import jnpf.database.datatype.db.interfaces.DtInterface;
import jnpf.database.datatype.sync.util.DtSyncUtil;
import jnpf.database.model.dbfield.DbFieldModel;
import jnpf.database.model.dbfield.JdbcColumnModel;
import jnpf.database.model.dbtable.DbTableFieldModel;
import jnpf.database.model.dbtable.JdbcTableModel;
import jnpf.database.model.dto.PrepareSqlDTO;
import jnpf.database.model.entity.DbLinkEntity;
import jnpf.database.source.DbBase;
import jnpf.database.sql.enums.base.SqlComEnum;
import jnpf.database.sql.util.SqlFastUtil;
import jnpf.database.util.ConnUtil;
import jnpf.database.util.DbTypeUtil;
import jnpf.database.util.JdbcUtil;
import jnpf.exception.DataException;
import jnpf.exception.DataTypeException;
import lombok.Cleanup;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.util.*;

/**
 * 数据同步
 *
 * @author JNPF开发平台组
 * @version V3.1.0
 * @copyright 引迈信息技术有限公司
 * @date 2019年9月27日 上午9:18
 */
@Slf4j
@Service
public class DbSyncServiceImpl implements DbSyncService {

    @Autowired
    private DbLinkService dblinkService;
    @Autowired
    private DbTableService dbTableService;

    private static Properties props;

    static {
        Properties props = new Properties();
        props.setProperty("remarks", "true"); //设置可以获取remarks信息
        props.setProperty("useInformationSchema", "true");//设置可以获取tables remarks信息
        DbSyncServiceImpl.props = props;
    }

    @Override
    public Integer checkExecute(String dbLinkIdFrom, String dbLinkIdTo, Map<String, String> convertRuleMap, String table) throws Exception {
        DbLinkEntity linkFrom = dblinkService.getInfo(dbLinkIdFrom);
        DbLinkEntity linkTo = dblinkService.getInfo(dbLinkIdTo);
        //验证一（同库无法同步数据）
        if (dbLinkIdFrom.equals(dbLinkIdTo)){
            return -1;
        }else{
            if(linkFrom.getHost().equals(linkTo.getHost()) &&
                    linkFrom.getPort().equals(linkTo.getPort()) &&
                    linkFrom.getDbName().equals(linkTo.getDbName())){
                return -1;
            }
        }
        @Cleanup Connection connFrom = ConnUtil.ConnCommon.getConnRemarks(linkFrom);
        @Cleanup Connection connTo = ConnUtil.ConnCommon.getConnRemarks(linkTo);
        //验证二（表存在）
        if (dbTableService.isExistTable(dbLinkIdTo, table)) {
            //验证三（验证表数据）
            if (SqlFastUtil.tableDataExist(connTo, table)) {
                //被同步表存在数据
                return 3;
            }
        }
        executeCommon(connFrom, connTo, convertRuleMap, table);
        return 0;
    }

    @Override
    public void executeImport(String dbLinkIdFrom, String dbLinkIdTo, Map<String, String> convertRuleMap, String table) throws Exception {
        @Cleanup Connection connFrom = ConnUtil.ConnCommon.getConnRemarks(dblinkService.getInfo(dbLinkIdFrom));
        @Cleanup Connection connTo = ConnUtil.ConnCommon.getConnRemarks(dblinkService.getInfo(dbLinkIdTo));
        executeCommon(connFrom, connTo, convertRuleMap, table);
    }

    @Override
    public Map<String, Integer> batchExecuteImport(String dbLinkIdFrom, String dbLinkIdTo, Map<String, String> convertRuleMap, List<String> tableList) throws Exception {
        @Cleanup Connection connFrom = ConnUtil.ConnCommon.getConnRemarks(dblinkService.getInfo(dbLinkIdFrom));
        @Cleanup Connection connTo = ConnUtil.ConnCommon.getConnRemarks(dblinkService.getInfo(dbLinkIdTo));

        Map<String, Integer> map = new HashMap<>(16);
        for (int i = 0; i < tableList.size(); i++) {
            String table = tableList.get(i);
            int total = tableList.size();
            try{
                executeCommon(connFrom, connTo, convertRuleMap, table);
                map.put(table, 1);
                System.out.println("表：（" + table + "）同步成功！" + "(" + i + "/" + total + ")");
            }catch (Exception e){
                e.printStackTrace();
                map.put(table, 0);
                System.out.println("表：（" + table + "）同步失败！" + "(" + i + "/" + total + ")");
            }
        }
        return map;
    }
    private void executeCommon(Connection connFrom, Connection connTo, Map<String, String> convertRuleMap, String table) throws Exception {
        String fromDbEncode = DbTypeUtil.getDb(connFrom).getJnpfDbEncode();
        String toDbEncode = DbTypeUtil.getDb(connTo).getJnpfDbEncode();
        // 删除To表
        try{
            SqlFastUtil.dropTable(connTo, table);
        }catch (Exception e){
            System.out.println(e.getMessage());
        }
        // 对数据类型进行处理

        DbTableFieldModel dbTableFieldModel =  new JdbcTableModel(connFrom, table).convertDbTableFieldModel();
        List<DbFieldModel> fields = dbTableFieldModel.getDbFieldModelList();

        // 规则Map里的（默认）去除
        if(convertRuleMap != null){
            convertRuleMap.forEach((key, val) ->{
                convertRuleMap.put(key, val.replace(" (默认)", ""));
            });
        }
        for (DbFieldModel field : fields) {
            try {
                DtInterface dtEnum = DtSyncUtil.getToCovert(fromDbEncode, toDbEncode, field.getDataType(), convertRuleMap);
                field.getViewDtModel().setDtEnum(dtEnum);
                field.setDataType(null);
                if(toDbEncode.equals(DbBase.MYSQL)){
                    checkMysqlFieldPrimary(field, table);
                }
            }catch (DataException d){
                System.out.println("表_" + table + ":" + d.getMessage());
                DataException dataException = new DataException("目前还未支持数据类型" + toDbEncode + "." + table + "（" + field.getDataType() + "）");
                dataException.printStackTrace();
                throw dataException;
                // 类型寻找失败转换成字符串
//                field.setDataType(new ViewDtModel().getStringFixedDt(toDbEncode));
            }catch (Exception e) {
                if(e instanceof DataTypeException){
                    throw e;
                }
                System.out.println(e.getMessage());
            }
        }

        //获取建表语句
        SqlFastUtil.creTable(connTo, dbTableFieldModel);
        //同步数据
        syncTableData(connFrom, connTo, table);
    }


    private void syncTableData(Connection connFrom, Connection connTo, String table) throws Exception {
        //初始库Form
        List<List<JdbcColumnModel>> modelList = JdbcUtil.queryIncludeFieldMods(new PrepareSqlDTO(connFrom, SqlComEnum.SELECT_TABLE.getOutSql(table)));
        if (modelList.size() > 0){
            // 获取字段集合
            List<String> fieldList = new LinkedList<>();
            for (JdbcColumnModel dbColumnModel : modelList.get(0)) {
                fieldList.add(dbColumnModel.getField());
            }
            // 获取参数集合
            DbBase fromDbBase = DbTypeUtil.getDb(connFrom);
            DbBase toDbBase = DbTypeUtil.getDb(connTo);
            List<List<?>> multiDataList = new ArrayList<>();
            for (List<JdbcColumnModel> dbColumnModels : modelList) {
                List<Object> dataList = new ArrayList<>();
                for (JdbcColumnModel dbColumnModel : dbColumnModels) {
                    dataList.add(convertValue(dbColumnModel, toDbBase));
                }
                multiDataList.add(dataList);
            }
            //被同步库同步To
            SqlFastUtil.batchInsert(connTo, table, fieldList, multiDataList);
        }
    }

    /* =========================== 字段值处理 ============================ */

    public static Object convertValue(JdbcColumnModel dbColumnModel, DbBase toDb) throws Exception {
        switch (toDb.getJnpfDbEncode()){
            case DbBase.ORACLE:
                return getOracleValue(dbColumnModel);
            case DbBase.POSTGRE_SQL:
                return getPostgreValue(dbColumnModel);
            case DbBase.MYSQL:
                return getMysqlValue(dbColumnModel);
            default:
        }
        return dbColumnModel.getValue();
    }

    /**
     * Oracle一些类型的特殊处理
     */
    public static Object getOracleValue(JdbcColumnModel dbColumnModel) {
        Object value = dbColumnModel.getValue();
        String dataType = dbColumnModel.getDataType();
        if(value != null){
            // 时间处理
            if (DtOracleEnum.DATE.getDataType().equalsIgnoreCase(dataType) || DtOracleEnum.TIMESTAMP.getDataType().equalsIgnoreCase(dataType)) {
                value = "TO_DATE('" + value + "','YYYY-MM-DD HH24:MI:SS')";
            }
            // 非空时空串报错，因Oracle空串存储为NULL，用一个空格代替空串
            if(value.equals("") && dbColumnModel.getNullSign().equals(DbFieldConst.NOT_NULL)){
                return  " ";
            }
        }
        return value;
    }

    /**
     * Postgre一些类型的特殊处理
     */
    public static Object getPostgreValue(JdbcColumnModel dbColumnModel) {
//        DtInterface dtEnum = DtInterface.newInstance(dataType, DbBase.MYSQL, false);
//        DtInterface toEnum = DtSyncUtil.getToFixCovert(dtEnum, DbBase.POSTGRE_SQL);
        return dbColumnModel.getValue();
    }

    /**
     * Mysql一些类型的特殊处理
     */
    public static Object getMysqlValue(JdbcColumnModel dbColumnModel) {
        return dbColumnModel.getValue();
    }

    public static void  checkMysqlFieldPrimary(DbFieldModel field, String table) throws DataTypeException {
        // Mysql对主键的一些限制
        if(field.getIsPrimaryKey()){
            // Mysql 字段为主键的时候，不能为BLOB/TEXT/tinytext的类型
            switch ((DtMySQLEnum)field.getViewDtModel().getDtEnum()){
                case BLOB:
                case TINY_TEXT:
                case MEDIUM_TEXT:
                case TEXT:
                case LONG_TEXT:
                    throw new DataTypeException("表 \"" + table + "\"中字段 \"" + field.getField() + "\" 为主键，不允许数据类型 \"" + field.getViewDtModel().getDtEnum().getDataType() + "\" ");
                case VARCHAR:
                    // varchar作为主键的时候，长度不允许超过768
                    if(field.getViewDtModel().getViewCharLength() > 768L){
                        field.getViewDtModel().setViewCharLength(768L);
                    }
                    break;
                default:
            }
        }
    }


}
